﻿using System;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;


namespace Volunteer
{
	public class DBConn
	{
        private string ConnectionString = ConfigurationManager.ConnectionStrings["VolunteerDBConnectionString"].ConnectionString;

        #region " Database Utilities "
        private string checkEscapeChar(string strInput)
        {
            if (strInput.Contains("'"))
            {
                strInput = strInput.Replace("'", "''");
            }
            return strInput;
        }
        #endregion

        #region " Hours Worked Stored Procedures "
        public void HoursWorked_Add(int intVID, DateTime dtDateFrom, DateTime dtDateTo, int CourtID, string JFName, string JLName, Double HoursWorked)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand sqlComm = new SqlCommand();


                sqlComm.Parameters.Add(new SqlParameter("@VID", intVID));
                sqlComm.Parameters.Add(new SqlParameter("@DateFrom", dtDateFrom));
                sqlComm.Parameters.Add(new SqlParameter("@DateTo", dtDateTo));
                sqlComm.Parameters.Add(new SqlParameter("@CourtID", CourtID));
                sqlComm.Parameters.Add(new SqlParameter("@JFName", JFName));
                sqlComm.Parameters.Add(new SqlParameter("@JLName", JLName));
                sqlComm.Parameters.Add(new SqlParameter("@HoursWorked", HoursWorked));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_HoursWorked_Add";


                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }

        public void HoursWorked_Update(int intVID, int CalID, int HoursWorked)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand sqlComm = new SqlCommand();

                sqlComm.Parameters.Add(new SqlParameter("@VID", intVID));
                sqlComm.Parameters.Add(new SqlParameter("@CalID", CalID));
                sqlComm.Parameters.Add(new SqlParameter("@hours_worked", HoursWorked));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_HoursWorked_Update";


                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }

        public void HoursWorked_Delete(int intVID)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand sqlComm = new SqlCommand();


                sqlComm.Parameters.Add(new SqlParameter("@VID", intVID));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_HoursWorked_Update";


                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }
#endregion

        #region " Volunteer Stored Procedures "
        public bool Volunteer_Add(VolunteerInfo v)
        {

            SqlConnection sqlConn = new SqlConnection(ConnectionString);
            int intNumRow = 0;

            try
            {
                SqlCommand sqlComm = new SqlCommand();

                //do validation of input data first before writing it to db
                v.LastName = checkEscapeChar(v.LastName);
                v.Street = checkEscapeChar(v.Street);
                
                sqlComm.Parameters.Add(new SqlParameter("@FName", v.FirstName));
                sqlComm.Parameters.Add(new SqlParameter("@LName", v.LastName));
                sqlComm.Parameters.Add(new SqlParameter("@Street", v.Street));
                sqlComm.Parameters.Add(new SqlParameter("@City", v.City));
                sqlComm.Parameters.Add(new SqlParameter("@State", v.State));
                sqlComm.Parameters.Add(new SqlParameter("@ZIP", v.Zip));
                sqlComm.Parameters.Add(new SqlParameter("@WorkPhone", v.WorkPhone));
                sqlComm.Parameters.Add(new SqlParameter("@CellPhone", v.CellPhone));
                sqlComm.Parameters.Add(new SqlParameter("@HomePhone", v.HomePhone));
                sqlComm.Parameters.Add(new SqlParameter("@Fax", v.Fax));
                sqlComm.Parameters.Add(new SqlParameter("@Email", v.Email));
                sqlComm.Parameters.Add(new SqlParameter("@Title", v.Title));
                sqlComm.Parameters.Add(new SqlParameter("@Status", v.Status));
                sqlComm.Parameters.Add(new SqlParameter("@Notes", v.Notes));
                sqlComm.Parameters.Add(new SqlParameter("@DOB", v.DOB));
                sqlComm.Parameters.Add(new SqlParameter("@HireDate", v.HireDate));
                sqlComm.Parameters.Add(new SqlParameter("@TermDate", v.TermDate));
                sqlComm.Parameters.Add(new SqlParameter("@EnteredBy", v.EnteredBy));
                sqlComm.Parameters.Add(new SqlParameter("@Ethnicity", v.Ethnicity));
                sqlComm.Parameters.Add(new SqlParameter("@AgeRange", v.AgeRange));
                sqlComm.Parameters.Add(new SqlParameter("@County", v.County));
                sqlComm.Parameters.Add(new SqlParameter("@Disability", v.Disability));
                sqlComm.Parameters.Add(new SqlParameter("@EducationLvl", v.EducationLevel));
                sqlComm.Parameters.Add(new SqlParameter("@Gender", v.Gender));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_Volunteer_Add";


                intNumRow = sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }

            if (intNumRow != 0)
            { return true; }
            else
            { return false; }
        }

        public void Volunteer_List(int intVID)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);


            try
            {
                SqlCommand sqlComm = new SqlCommand();

                sqlComm.Parameters.Add(new SqlParameter("@vid", intVID));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_Volunteer_List";


                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }

        public void Volunteer_Delete(int intVID)
        {
            SqlConnection sqlConn = new SqlConnection(ConnectionString);


            try
            {
                SqlCommand sqlComm = new SqlCommand();

                sqlComm.Parameters.Add(new SqlParameter("@vid", intVID));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_Volunteer_Delete";


                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }

        public void Volunteer_Update(VolunteerInfo v)
        {

            SqlConnection sqlConn = new SqlConnection(ConnectionString);


            try
            {
                SqlCommand sqlComm = new SqlCommand();

                sqlComm.Parameters.Add(new SqlParameter("@vid", v.VID));
                sqlComm.Parameters.Add(new SqlParameter("@FName", v.FirstName));
                sqlComm.Parameters.Add(new SqlParameter("@LName", v.LastName));
                sqlComm.Parameters.Add(new SqlParameter("@Street", v.Street));
                sqlComm.Parameters.Add(new SqlParameter("@City", v.City));
                sqlComm.Parameters.Add(new SqlParameter("@State", v.State));
                sqlComm.Parameters.Add(new SqlParameter("@ZIP", v.Zip));
                sqlComm.Parameters.Add(new SqlParameter("@WorkPhone", v.WorkPhone));
                sqlComm.Parameters.Add(new SqlParameter("@CellPhone", v.CellPhone));
                sqlComm.Parameters.Add(new SqlParameter("@HomePhone", v.HomePhone));
                sqlComm.Parameters.Add(new SqlParameter("@Fax", v.Fax));
                sqlComm.Parameters.Add(new SqlParameter("@Email", v.Email));
                sqlComm.Parameters.Add(new SqlParameter("@Title", v.Title));
                sqlComm.Parameters.Add(new SqlParameter("@Status", v.Status));
                sqlComm.Parameters.Add(new SqlParameter("@Notes", v.Notes));
                sqlComm.Parameters.Add(new SqlParameter("@DOB", v.DOB));
                sqlComm.Parameters.Add(new SqlParameter("@HireDate", v.HireDate));
                sqlComm.Parameters.Add(new SqlParameter("@TermDate", v.TermDate));
                sqlComm.Parameters.Add(new SqlParameter("@EnteredBy", v.EnteredBy));
                sqlComm.Parameters.Add(new SqlParameter("@Ethnicity", v.Ethnicity));
                sqlComm.Parameters.Add(new SqlParameter("@AgeRange", v.AgeRange));
                sqlComm.Parameters.Add(new SqlParameter("@County", v.County));
                sqlComm.Parameters.Add(new SqlParameter("@Disability", v.Disability));
                sqlComm.Parameters.Add(new SqlParameter("@EducationLvl", v.EducationLevel));
                sqlComm.Parameters.Add(new SqlParameter("@Gender", v.Gender));

                sqlConn.Open();
                sqlComm.Connection = sqlConn;
                sqlComm.CommandType = CommandType.StoredProcedure;
                sqlComm.CommandText = "up_Volunteer_Update";

                sqlComm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }
        #endregion

    }
}
